﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace FilmManagement2.Founction
{

    public class ConnectToSql
    {
        public  string getsql = "Data Source=PC-201004271819;Initial Catalog=FilmManageMent2;Integrated Security=True";

        public bool AdminLog(string adminaccount,string adminps)
        {
            bool b = false;
            string str="select count(*) from Admin where AdminAccount='"+adminaccount+"'and AdminPassword='"+adminps+"'";
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            SqlCommand sqcom = new SqlCommand(str,sqlcon);
           // sqcom.ExecuteScalar();
            if ( Convert.ToInt32(sqcom.ExecuteScalar())>0)
            {
                b = true;
            }
            
            sqlcon.Close();
            return b;
        }
        public bool AddSugest(string content,DateTime dt)
        {
            bool b = false;
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "insert into Suggestion values('"+content+"','"+dt+"') ";
            SqlCommand sqlcom = new SqlCommand(str,sqlcon);
            if (sqlcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqlcon.Close();
            return b;
        
        }
        
        public bool AddFilm(string filmname,string leaderactor,string details,string picturepath,decimal price)
        {
            bool b = true;
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();

            string str2 = "select count(*) from Film where PictureSource='" + picturepath + "'";
            SqlCommand sqcom2 = new SqlCommand(str2, sqlcon);
            if (Convert.ToInt32(sqcom2.ExecuteScalar()) == 0)
            {
                string str = "INSERT INTO Film VALUES ('" + filmname + "','" + leaderactor + "','" + details + "','" + picturepath + "','"+price+"')";
                SqlCommand sqcom = new SqlCommand(str, sqlcon);
                if (sqcom.ExecuteNonQuery() > 0)
                {
                    b = true;
                }
                else
                    b = false;
            }
            else
            {
                b = false;

            }


            sqlcon.Close();
            return b;
        
        }
        public bool UpdateFilm(int filmid, string filmname, string leaderactor, string details,decimal price)
        {
            bool b = false;
            SqlConnection sqcon = new SqlConnection(getsql);
            sqcon.Open();
            string str = "update Film set FilmName='" + filmname + "',LeadActors='" + leaderactor + "',Details='" + details + "', Price='"+price+"' where  FilmId='" + filmid + "'";
            SqlCommand sqcom = new SqlCommand(str, sqcon);
            if (sqcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqcon.Close();
            return b;
        
        }
        public bool UpdateFilm(int filmid,string filmname,string leaderactor,string details,string picturepath,decimal pirce)
        {
            bool b = false;
            SqlConnection sqcon = new SqlConnection(getsql);
            sqcon.Open();
             string str2 = "select count(*) from Film where PictureSource='" + picturepath + "'";
            SqlCommand sqcom2 = new SqlCommand(str2, sqcon);
            if (Convert.ToInt32(sqcom2.ExecuteScalar()) == 0)
            {
                string str = "update Film set FilmName='" + filmname + "',LeadActors='" + leaderactor + "',Details='" + details + "',PictureSource='" + picturepath + "',Price='"+pirce+"' where  FilmId='" + filmid + "'";

                SqlCommand sqcom = new SqlCommand(str, sqcon);
                if (sqcom.ExecuteNonQuery() > 0)
                {
                    b = true;
                }

            }

            else
            {
                b = false;
            }
            sqcon.Close();
            return b;
        
        
        }
        public DataSet GetFilm()
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from film";
            SqlDataAdapter sda = new SqlDataAdapter(str,sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        
        }
     
        public DataSet GetFilmByID(int filmId)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from Film where FilmId='"+filmId+"'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        
        
        }
        public DataSet GetStudioOneDetailByID(int filmId)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from MovieStudioOne where FilmId='" + filmId + "'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;   
      }
        
        public DataSet GetStudioTwoDetailByID(int filmId)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from MovieStudioTwo where FilmId='" + filmId + "'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
        public DataSet GetStudioThreeDetailByID(int filmId)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from MovieStudioThree where FilmId='" + filmId + "'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
        public DataSet GetStudioOneDetailByIDandStartTime(int filmid,DateTime starttime)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from MovieStudioOne where FilmId='" + filmid + "' and StartTime='"+starttime+"'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
        public DataSet GetStudioTwoDetailByIDandStartTime(int filmid, DateTime starttime)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from MovieStudioTwo where FilmId='" + filmid + "' and StartTime='" + starttime + "'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
        public DataSet GetStudioThreeDetailByIDandStartTime(int filmid, DateTime starttime)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select * from MovieStudioThree where FilmId='" + filmid + "' and StartTime='" + starttime + "'";
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }

        public bool UpdateStudioOne(int id, DateTime starttime,DateTime endtime,string seat,bool seatstatus)
        {
            bool b = false;
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = " update MovieStudioOne set StartTime='"+starttime+"',EndTime='"+endtime+"',"+seat+"='"+seatstatus+"' where StudioOneId='"+id+"'   ";
            SqlCommand sqlcom = new SqlCommand(str,sqlcon);
            if (sqlcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqlcon.Close();
            return b;
        }

        public bool UpdateStudioTwo(int id, DateTime starttime, DateTime endtime, string seat, bool seatstatus)
        {
            bool b = false;
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = " update MovieStudioTwo set StartTime='" + starttime + "',EndTime='" + endtime + "'," + seat + "='" + seatstatus + "' where StudioTwoId='" + id + "'   ";
            SqlCommand sqlcom = new SqlCommand(str, sqlcon);
            if (sqlcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqlcon.Close();
            return b;
        }
        public bool UpdateStudioThree(int id, DateTime starttime, DateTime endtime, string seat, bool seatstatus)
        {
            bool b = false;
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = " update MovieStudioThree set StartTime='" + starttime + "',EndTime='" + endtime + "'," + seat + "='" + seatstatus + "' where StudioThreeId='" + id + "'   ";
            SqlCommand sqlcom = new SqlCommand(str, sqlcon);
            if (sqlcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqlcon.Close();
            return b;
        }

        public string GetPicturePathByID(int filmId)
        {
            string path = "";
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            string str = "select PictureSource from Film where FilmId='" + filmId + "'";
            SqlCommand sqcm = new SqlCommand(str,sqlcon);
           path= sqcm.ExecuteScalar().ToString();
           sqlcon.Close();
           return path;
        }
        public bool AddAdmin(string adminAccount,string adminPs)
        {
            bool b = true;
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();

            string str2 = "select count(*) from Admin where AdminAccount='" + adminAccount + "'";
            SqlCommand sqcom2 = new SqlCommand(str2, sqlcon);
            if (Convert.ToInt32(sqcom2.ExecuteScalar()) == 0)
            {
                string str = "INSERT INTO Admin VALUES ('" + adminAccount + "','" + adminPs + "')";
                SqlCommand sqcom = new SqlCommand(str, sqlcon);
                if (sqcom.ExecuteNonQuery() > 0)
                {
                    b = true;
                }
                else
                    b = false;
            }
            else
            {
                b = false;
                
            }


            sqlcon.Close();
            return b;
        }
        public bool AddStudioOne(int filmid,DateTime starttime,DateTime endtime)
        {
            bool b = false;
            string str = "insert into MovieStudioOne values('" + filmid + "','" + starttime + "','" + endtime + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "')";
            SqlConnection sql = new SqlConnection(getsql);
            sql.Open();
            SqlCommand sqlcomo = new SqlCommand(str,sql);
            if (sqlcomo.ExecuteNonQuery() > 0)
            {
                b = true;           
            }
            sql.Close();
            return b;
        
        }
        public bool AddStudioTwo(int filmid, DateTime starttime, DateTime endtime)
        {
            bool b = false;
            string str = "insert into MovieStudioTwo values('" + filmid + "','" + starttime + "','" + endtime + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "')";
            SqlConnection sql = new SqlConnection(getsql);
            sql.Open();
            SqlCommand sqlcomo = new SqlCommand(str, sql);
            if (sqlcomo.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sql.Close();
            return b;

        }
        public bool AddStudioThree(int filmid, DateTime starttime, DateTime endtime)
        {
            bool b = false;
            string str = "insert into MovieStudioThree values('" + filmid + "','" + starttime + "','" + endtime + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "','" + true + "')";
            SqlConnection sql = new SqlConnection(getsql);
            sql.Open();
            SqlCommand sqlcomo = new SqlCommand(str, sql);
            if (sqlcomo.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sql.Close();
            return b;

        }

        public bool AddTicketList(int filmid, int studioid, string seat, DateTime starttime, string custidentity, bool getticketstatus,decimal price)
        {
            bool b = false;
            string str="insert into GetTicketList values('"+filmid+"','"+studioid+"','"+seat+"','"+starttime+"','"+custidentity+"','"+getticketstatus+"','"+price+"')";
            SqlConnection sqcon = new SqlConnection(getsql);
            sqcon.Open();
            SqlCommand sqcom=new SqlCommand(str,sqcon);
           if(sqcom.ExecuteNonQuery()>0)
           {
            b=true;
           }
           sqcon.Close();
           return b;
        }
        public bool UpdateMovieStudioOne(int filmid, DateTime starttime,string columnname)
        {
            bool b = false;
            string str = "update MovieStudioOne set "+columnname+"= 'false' where FilmId='"+filmid+"' and StartTime='"+starttime+"'";

            SqlConnection sqcon = new SqlConnection(getsql);
            sqcon.Open();
            SqlCommand sqcom = new SqlCommand(str, sqcon);
            if (sqcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqcon.Close();
            return b;
    }
        public bool UpdateMovieStudioTwo(int filmid, DateTime starttime, string columnname)
        {
            bool b = false;
            string str = "update MovieStudioTwo set " + columnname + "= 'false' where FilmId='" + filmid + "' and StartTime='" + starttime + "'";

            SqlConnection sqcon = new SqlConnection(getsql);
            sqcon.Open();
            SqlCommand sqcom = new SqlCommand(str, sqcon);
            if (sqcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqcon.Close();
            return b;
        }
        public bool UpdateMovieStudioThree(int filmid, DateTime starttime, string columnname)
        {
            bool b = false;
            string str = "update MovieStudioThree set " + columnname + "= 'false' where FilmId='" + filmid + "' and StartTime='" + starttime + "'";

            SqlConnection sqcon = new SqlConnection(getsql);
            sqcon.Open();
            SqlCommand sqcom = new SqlCommand(str, sqcon);
            if (sqcom.ExecuteNonQuery() > 0)
            {
                b = true;
            }
            sqcon.Close();
            return b;
        }
        public DataSet GetMovieStudioOneByStduioId(int id)
        {
          
            DataSet ds = new DataSet();
            string str = "select * from MovieStudioOne where StudioOneId='" + id + "'";
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();      
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
        public DataSet GetMovieStudioTwoByStduioId(int id)
        {
        
            DataSet ds = new DataSet();
            string str = "select * from MovieStudioTwo where StudioTwoId='" + id + "'";
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
        public DataSet GetMovieStudioThreeByStduioId(int id)
        {
        
            DataSet ds = new DataSet();
            string str = "select * from MovieStudioThree where StudioThreeId='" + id + "'";
            SqlConnection sqlcon = new SqlConnection(getsql);
            sqlcon.Open();
            SqlDataAdapter sda = new SqlDataAdapter(str, sqlcon);
            sda.Fill(ds);
            sqlcon.Close();
            return ds;
        }
    }
}